unit OrtalamaTamir;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, ADODB, wwdblook, StdCtrls, wwdbdatetimepicker, ppDB, ppDBPipe, ppVar,
  ppBands, ppCtrls, ppPrnabl, ppClass, ppCache, ppComm, ppRelatv, ppProd,
  ppReport, Buttons, Grids, DBGrids, ppParameter, Math;

type
  TOrtalamaTamirF = class(TForm)
    Label15: TLabel;
    Label14: TLabel;
    BitTar: TwwDBDateTimePicker;
    basTar: TwwDBDateTimePicker;
    CheckBox2: TCheckBox;
    tipi: TwwDBLookupCombo;
    Tekniktipi: TADOQuery;
    Personel: TADOQuery;
    CheckBox1: TCheckBox;
    teknisyen: TwwDBLookupCombo;
    BitBtn1: TBitBtn;
    qTekLstS: TDataSource;
    qTekLst: TADOQuery;
    pptekrapor: TppReport;
    ppTitleBand1: TppTitleBand;
    ppLabel1: TppLabel;
    ppLabel4: TppLabel;
    ppLabel5: TppLabel;
    d1: TppLabel;
    d2: TppLabel;
    ppLabel8: TppLabel;
    d3: TppLabel;
    ppHeaderBand1: TppHeaderBand;
    ppLine1: TppLine;
    ppLabel2: TppLabel;
    ppLabel3: TppLabel;
    ppDetailBand1: TppDetailBand;
    ppDBText1: TppDBText;
    ppDBText2: TppDBText;
    ppFooterBand1: TppFooterBand;
    ppLine2: TppLine;
    ppSystemVariable1: TppSystemVariable;
    ppSystemVariable2: TppSystemVariable;
    ppDBPipeline1: TppDBPipeline;
    Label1: TLabel;
    Label2: TLabel;
    Button1: TButton;
    DBGrid1: TDBGrid;
    Memo1: TMemo;
    Label3: TLabel;
    edGun: TEdit;
    Button2: TButton;
    ppParameterList1: TppParameterList;
    ppLabel6: TppLabel;
    ppDBText3: TppDBText;
    Gun: TLabel;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormShow(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure CheckBox2Click(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure basTarChange(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  OrtalamaTamirF: TOrtalamaTamirF;

implementation
uses Dateutils;
{$R *.DFM}

procedure TOrtalamaTamirF.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  Tekniktipi.close;
  Personel.close;

  Action := cafree;
  OrtalamaTamirF := nil;
end;

procedure TOrtalamaTamirF.FormShow(Sender: TObject);
begin
  Tekniktipi.close;
  Tekniktipi.open;

  Personel.close;
  Personel.open;

end;

procedure TOrtalamaTamirF.BitBtn1Click(Sender: TObject);
var T, OrtGun, Counter: integer;
  Toplam, Gercek, Pa: Currency;
  date1, date2: TDateTime;
label Atla;
begin
  for Counter := 1 to 2 do
  begin
    qTekLst.Close;
    qTekLst.SQL.Clear;
    qTekLst.SQL.Add('select Formno,dbo.GetSmallDate(tarih) Tarih,bittarih=dbo.GetSmallDate(isnull((select top 1 bittarih from onarim where onarim.formno=teknik.formno order by onarimno desc),Getdate())) into #yeni  from teknik ');

    if Counter = 1 then
    begin
      qTekLst.SQL.Add(' where ((durumu=''T'') or (durumu=''G'')) ');
      qTekLst.SQL.Add(' and (tarih>=:bastar) and (tarih<=:bittar)');
    end
    else
    begin
      qTekLst.SQL.Add(' where ((((durumu=''T'') or (durumu=''G''))  and (tarih>=:bastar) and (tarih<=:bittar)) or ');
      qTekLst.SQL.Add(' ((tarih>=:bastar1) and (tarih<=:bittar1 - ' + inttostr(OrtGun) + ')))');
    end;

    qTekLst.SQL.Add(' and subeid=1 and carikodgir<>12810 and carikodgir<>2054 ');

    if CheckBox2.Checked then
      qTekLst.SQL.Add('and (teknik.Tipi=''' + Tipi.LookupValue + ''')');
    if CheckBox1.Checked then
      qTekLst.SQL.Add(' and ((select top 1 tamireden from onarim where onarim.formno=teknik.formno order by onarimno desc))=' + #39 + Teknisyen.LookupValue + #39);
    qTekLst.SQL.Add(' Select * From #yeni Drop Table #yeni');
  //qTekLst.SQL.Add('select sum(datediff(dd,tarih,bittarih)),count(*) from #yeni where datediff(dd,tarih,bittarih)<30');

    qTekLst.Parameters.ParamByName('BasTar').datatype := ftDate;
    qTekLst.Parameters.ParamByName('BitTar').datatype := ftDate;
    qTekLst.Parameters.ParamByName('BasTar').value := BasTar.date;
    qTekLst.Parameters.ParamByName('BitTar').value := BitTar.date + 1;

    if Counter = 2 then
    begin
      qTekLst.Parameters.ParamByName('BasTar1').datatype := ftDate;
      qTekLst.Parameters.ParamByName('BitTar1').datatype := ftDate;
      qTekLst.Parameters.ParamByName('BasTar1').value := BasTar.date;
      qTekLst.Parameters.ParamByName('BitTar1').value := BitTar.date + 1;
    end;

    memo1.Lines := qteklst.sql;
    qTekLst.Open;

    Toplam := 0;
    qTekLst.First;
    while not qTekLst.Eof do
    begin
      if qTekLst.Fieldbyname('tarih').isnull or qTekLst.Fieldbyname('bittarih').isnull then goto Atla;
      if (trim(qTekLst.Fieldbyname('tarih').asstring) = '') or (trim(qTekLst.Fieldbyname('bittarih').asstring) = '') then goto Atla;

      Date1 := qTekLst.FieldValues['tarih'];
      date2 := qTekLst.FieldValues['bittarih'];

      Pa := 0;
      for t := 0 to DaysBetween(date2, date1) do
      begin
        if DayMonday = DayOfWeek(date1 + T) then
          Pa := Pa + 1;

        if DaySunday = DayOfWeek(date1 + T) then
          Pa := Pa + 0.5;
      end;

      Gercek := DaysBetween(date2, date1) - Pa;
      Toplam := Toplam + Gercek;

//      if Counter = 2 then
//        ShowMessage(qTekLst.FieldByName('Formno').AsString + '-' + inttostr(gercek) + '-' + qTekLst.Fieldbyname('tarih').asstring + '-' + qTekLst.Fieldbyname('bittarih').asstring);

      Atla:
      qTekLst.Next;
    end;

    if Counter = 1 then
      OrtGun := Ceil(Toplam / qTekLst.RecordCount);

    label2.Caption := floattostr(Toplam / qTekLst.RecordCount);
  end;
end;

procedure TOrtalamaTamirF.CheckBox2Click(Sender: TObject);
begin
  if CheckBox2.Checked then
  begin
    tipi.Enabled := true;
    tipi.SetFocus;
  end
  else
  begin
    tipi.value := '';
    tipi.Enabled := False;
  end;

end;

procedure TOrtalamaTamirF.CheckBox1Click(Sender: TObject);
begin
  if CheckBox1.Checked then
  begin
    teknisyen.Enabled := true;
    teknisyen.SetFocus;
  end
  else
  begin
    teknisyen.value := '';
    teknisyen.Enabled := False;
  end;

end;

procedure TOrtalamaTamirF.Button1Click(Sender: TObject);
begin
  close;
end;

procedure TOrtalamaTamirF.Button2Click(Sender: TObject);
begin
  d1.Caption := basTar.Text;
  d2.Caption := BitTar.Text;
  d3.Caption := tipi.Text;

  qTekLst.Close;
  qTekLst.SQL.Clear;
  qTekLst.SQL.Add('select tarih,bittarih=(select top 1 bittarih from onarim where onarim.formno=teknik.formno order by onarimno desc) into #yeni  from teknik ');
  qTekLst.SQL.Add('where ((durumu=''T'') or (durumu=''G'')) ');
  qTekLst.SQL.Add('and (tarih>:bastar) and (tarih<:bittar)');

  if CheckBox2.Checked then
    qTekLst.SQL.Add('and (teknik.Tipi=''' + Tipi.LookupValue + ''')');
  if CheckBox1.Checked then
    qTekLst.SQL.Add(' and ((select top 1 tamireden from onarim where onarim.formno=teknik.formno order by onarimno desc))=' + #39 + Teknisyen.LookupValue + #39);

  qTekLst.SQL.Add('select sum(datediff(dd,tarih,bittarih)),count(*) from #yeni where datediff(dd,tarih,bittarih)<30');

  qTekLst.Parameters.ParamByName('BasTar').datatype := ftDate;
  qTekLst.Parameters.ParamByName('BitTar').datatype := ftDate;
  qTekLst.Parameters.ParamByName('BasTar').value := BasTar.date;
  qTekLst.Parameters.ParamByName('BitTar').value := BitTar.date + 1;

  memo1.Lines := qteklst.sql;
  qTekLst.Open;
end;

procedure TOrtalamaTamirF.basTarChange(Sender: TObject);
begin
  if (BitTar.Text <> '') and (BasTar.Text <> '') then
    Gun.Caption := IntToStr(DaysBetween(BitTar.Date, basTar.Date));
end;

end.

